package no.hib.bridge.DAO;

import java.io.File;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.sql.PreparedStatement;

import no.hib.bridge.beans.BoardInfoBean;
import no.hib.bridge.beans.PlayerInfoBean;
import no.hib.bridge.beans.ResultBean;

public class BridgeAccessDAO {

	private Connection conDatabase;
	private ResultSet rs;
	private File file;
	private String sURL;
	private PreparedStatement ps;

	public BridgeAccessDAO(String filePath) throws FileNotFoundException {
		file = new File(filePath);

		sURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
		sURL += file.getPath().trim() + ";DriverID=22}";

	}


	/**
	 * 
	 * @param section
	 *            section letter!!
	 * @param round
	 *            round number
	 * @param table
	 *            table number
	 * @return a vector of players at a table that round
	 */
	public Vector<PlayerInfoBean> getPlayerInfo(String section, int table, int round) {
		Vector<PlayerInfoBean> list = new Vector<PlayerInfoBean>();

		try {
			// set this to connection string to the database

			// create connection to database using connection string
			conDatabase = DriverManager.getConnection(sURL, "", "");

			String query = "select pn.Direction, pn.Number, rd.Table, pn.custom_PairNumber, pn.Section, rd.NSPair, rd.EWPair "
					+ "from PlayerNumbers as pn, RoundData as rd, Section as s "
					+ "where s.ID=pn.section "
					+ "AND rd.Section=pn.Section "
					+ "AND (pn.custom_PairNumber=rd.NSPair OR pn.custom_PairNumber=rd.EWPair) "
					+ "AND s.Letter=? " + "AND rd.Table=? " + "AND rd.Round=?";

			ps = conDatabase.prepareStatement(query);
			ps.setString(1, section);
			ps.setInt(2, table);
			ps.setInt(3, round);
			ps.executeQuery();
			conDatabase.commit();

			rs = ps.getResultSet();

			while (rs.next()) {
				PlayerInfoBean pib = new PlayerInfoBean();
				pib.setPosition(rs.getString(1));
				pib.setNumber(rs.getString(2));
				pib.setTable(rs.getString(3));
				pib.setPair(rs.getString(4));
				pib.setSectionNr(rs.getInt(5));
				if(rs.getString(6).equalsIgnoreCase(pib.getPair())){
					if(pib.getPosition().equalsIgnoreCase("N")||pib.getPosition().equalsIgnoreCase("E")){
						pib.setPosition("N");
					}else{
						pib.setPosition("S");
					}
				}else{
					if(pib.getPosition().equalsIgnoreCase("N")||pib.getPosition().equalsIgnoreCase("E")){
						pib.setPosition("E");
					}else{
						pib.setPosition("W");
					}
				}
				if(pib.getNumber()!=null){
					list.addElement(pib);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				conDatabase.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	public PlayerInfoBean getPlayerName(PlayerInfoBean pib) {

		String navnRes = "";
		try {
			/*
			 * this method uses another DB file where the member numbers are
			 */
			String nameDB = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="
				     + System.getProperty("user.home")
				     + "\\Documents\\Ruter -- Dokument\\Ruter.mdb;DriverID=22}";
			// create connection to database using connection string
			conDatabase = DriverManager.getConnection(nameDB, "", "");

			String query = "select *" + " from Player" + " where MemberID = ?;";

			ps = conDatabase.prepareStatement(query);
			ps.setString(1, pib.getNumber());
			ps.executeQuery();
			conDatabase.commit();

			rs = ps.getResultSet();
			if (rs.next()) {
				navnRes += rs.getString(7) + " " + rs.getString(6);
				pib.setName(navnRes);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				conDatabase.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return pib;
	}

	public Vector<PlayerInfoBean> getPlayerInfoWithName(String section,
			int table, int round) {
		Vector<PlayerInfoBean> list = getPlayerInfo(section, table, round);
		Vector<PlayerInfoBean> result = new Vector<PlayerInfoBean>();

		while (list.size() > 0) {
			result.add(getPlayerName(list.remove(0)));
		}

		return result;
	}

	public void insertIntoReceived(ResultBean rB) {
		boolean update = checkUpdateReceived(rB.getSection(), rB.getTable(),
				rB.getRound(), rB.getBoard());
		try {



			// create connection to database using connection string
			conDatabase = DriverManager.getConnection(sURL, "", "");

			// System.out.println(checkUpdateReceived(rB.getSectionNr(),
			// rB.getTable(), rB.getRound(), rB.getBoard()));

			if (update) {
				/*String query = "UPDATE ReceivedData SET [NS/EW]=?, Contract=?, Result=? "
						+ "WHERE Section=? AND Table=? AND Round=? AND Board=?;";
				ps = conDatabase.prepareStatement(query);
				ps.setString(1, rB.getNSEW());
				if(rB.getContractNr().contains("pass")){
					ps.setString(2, "PASS");
				}else{
					ps.setString(2, rB.getContractNr() + " " + rB.getContractSuit()
						+ " " + rB.getRedouble());
				}
				
				ps.setString(3, rB.getResult());
				ps.setInt(4, rB.getSectionNr());
				ps.setInt(5, Integer.parseInt(rB.getTable()));
				ps.setInt(6, Integer.parseInt(rB.getRound()));
				ps.setInt(7, Integer.parseInt(rB.getBoard()));*/
			} else {
				String query = "INSERT INTO ReceivedData ([Section], [Table], Round, Board, [NS/EW], Contract, Result)"
						+ " VALUES (?, ?, ?, ?, ?, ?, ?)";
				ps = conDatabase.prepareStatement(query);
				ps.setInt(1, rB.getSectionNr());
				ps.setInt(2, Integer.parseInt(rB.getTable()));
				ps.setInt(3, Integer.parseInt(rB.getRound()));
				ps.setInt(4, Integer.parseInt(rB.getBoard()));
				ps.setString(5, rB.getNSEW());
				if(rB.getContractNr().contains("pass")||rB.getContractNr().contains("PASS")){
					ps.setString(6, "PASS");
				}else{
					ps.setString(6, rB.getContractNr() + " " + rB.getContractSuit()
						+ " " + rB.getRedouble());
				}
				ps.setString(7, rB.getResult());
				ps.executeUpdate();
				conDatabase.commit();
			}

			

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				conDatabase.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * @return true if result has already been posted
	 */
	public boolean checkUpdateReceived(String section, String table, String round,
			String board) {
		boolean update = false;
		try {
			// create connection to database using connection string
			conDatabase = DriverManager.getConnection(sURL, "", "");

			// setup java.sql.Statement to run queries

			String q = "select * " + "from ReceivedData as rd, Section as s "
					+ "where rd.section=s.ID" +
					" and s.letter=? and table=? and round=? and board=?;";

			ps = conDatabase.prepareStatement(q);
			ps.setString(1, section);
			ps.setString(2, table);
			ps.setString(3, round);
			ps.setString(4, board);
			rs = ps.executeQuery();
			update = rs.next();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				conDatabase.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return update;
	}

	public void updatePlayer(int section, String table, String direction,
			String memberNr) {
		try {

			// create connection to database using connection string
			conDatabase = DriverManager.getConnection(sURL, "", "");

			String query = "update PlayerNumbers" + " set [Number]=?"
					+ " where Section=? and Table = ? and Direction=?;";

			ps = conDatabase.prepareStatement(query);
			ps.setString(1, memberNr);
			ps.setInt(2, section);
			ps.setInt(3, Integer.parseInt(table));
			ps.setString(4, direction);

			ps.executeUpdate();
			conDatabase.commit();

			// rs = ps.getResultSet();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				conDatabase.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public boolean setNewFile(String filePath) {
		File f = new File(filePath);
		boolean ok = false;
		if (f.exists()) {
			file = f;
			ok = true;
		}
		return ok;
	}

	public int getSectionID(String section) {
		int sectionNumber = 0;
		try {

			// create connection to database using connection string
			conDatabase = DriverManager.getConnection(sURL, "", "");

			// setup java.sql.Statement to run queries
			String IDCheckQ = "select * " + "from Section " + "where Letter=?;";
			ps = conDatabase.prepareStatement(IDCheckQ);
			ps.setString(1, section);
			ps.executeQuery();
			conDatabase.commit();
			rs = ps.getResultSet();
			if(rs.next()){
				sectionNumber = rs.getInt("ID");
			}
			
			if (sectionNumber == 0) {
				throw new Exception("dao getSection(String s): couldnt find section");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (Exception e) {
			return 0;
		} finally {
			try {
				ps.close();
				conDatabase.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return sectionNumber;
	}
	
	public BoardInfoBean getGameInfo(BoardInfoBean bi) {
		try {

			// create connection to database using connection string
			conDatabase = DriverManager.getConnection(sURL, "", "");

			// setup java.sql.Statement to run queries

			String q = "select LowBoard, HighBoard " +
					"from RoundData as rd, Section as s " +
					"where s.ID=rd.section and s.letter=? and Table=? and Round=?;";

			ps = conDatabase.prepareStatement(q);
			ps.setString(1, bi.getSection());
			ps.setString(2, bi.getTable());
			ps.setString(3, bi.getRound());
			rs = ps.executeQuery();
			if(rs.next()){
				bi.setLowBoard(rs.getInt(1));
				bi.setHighBoard(rs.getInt(2));
				String name=file.getName();
				bi.fileName=name.substring(0, name.length()-4);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				conDatabase.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bi;
	}

	
	
	
	

	/*
	 * public void connectToBws(String filePath) { boolean fileOK = false;
	 * 
	 * if (file.exists()) { fileOK = true; }
	 * 
	 * if (fileOK) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	 * 
	 * // set this to connection string to the database
	 * 
	 * String sURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
	 * sURL += filePath.trim() + ";DriverID=22}";
	 * 
	 * // create connection to database using connection string conDatabase =
	 * DriverManager.getConnection(sURL, "", "");
	 * 
	 * // setup java.sql.Statement to run queries st =
	 * conDatabase.createStatement();
	 * 
	 * st.close(); conDatabase.close(); } catch (Exception e) { // TODO
	 * Auto-generated catch block e.printStackTrace(); } } } /* public String
	 * getPlayerInfo(int round, int table, int section){ Connection conDatabase;
	 * Statement st; ResultSet rs;
	 * 
	 * //ny bean
	 * 
	 * try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	 * 
	 * // set this to connection string to the database
	 * 
	 * String sURL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
	 * sURL += filePath.trim() + ";DriverID=22}"; System.out.println("url: " +
	 * sURL);
	 * 
	 * // create connection to database using connection string conDatabase =
	 * DriverManager.getConnection(sURL, "", "");
	 * 
	 * // setup java.sql.Statement to run queries st =
	 * conDatabase.createStatement(); //obs sql injection
	 * st.execute("select pn.Direction, pn.Name" +
	 * " from PlayerNumbers pn, RoundData rd" +
	 * " where pn.Section=rd.Section AND pn.Table=rd.Table and rd.Round="
	 * +round+" and rd.Table="+table+";"); rs = st.getResultSet();
	 * while(rs.next()){ System.out.println(rs.getString(1));
	 * System.out.println(rs.getString(2));
	 * System.out.println("-------------------"); }
	 * 
	 * 
	 * st.close(); conDatabase.close(); } catch (Exception e) { // TODO
	 * Auto-generated catch block e.printStackTrace(); } //return bean }
	 */

	public void insertIntoReceivedFromList(String[] s) {
		ResultBean rB = new ResultBean(Integer.parseInt(s[0]), s[1], s[2],
				s[3], s[4], s[5], s[6], s[7], s[8]);
		insertIntoReceived(rB);

		// insertToReceived(1, 1, 1, 1, "2 N", "+1");

	}


	public int getLastRoundNumber() {
		int lastRound = -1;
		try {
			
			// create connection to database using connection string
			conDatabase = DriverManager.getConnection(sURL, "", "");

			// setup java.sql.Statement to run queries

			String q = 	"select DISTINCT Round " +
						"from RoundData;";

			ps = conDatabase.prepareStatement(q);
			rs = ps.executeQuery();
			while(rs.next()){
				int temp = rs.getInt(1);
				if(temp>lastRound){
					lastRound=temp;
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				ps.close();
				conDatabase.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return lastRound;
	}
}
